package spatialmarketplace.cem.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import spatialmarketplace.cem.model.Group;
import spatialmarketplace.cem.model.GroupMember;

/**
 * Class that connects to the database server
 *
 * @param username
 * @param pwd @paramconnectionURL
 * @param driver
 * @param dbName
 * @return conn
 *
 * @author Camila
 */
public class DBHandler {

    /**
     * ******** LTU DB Server Configuration *********
     */
    private static DBHandler instance = null;
    private Connection conn = null;
    private static String username = new String("m7gonzalezyanez");
    private static String pwd = new String("UggX7VFDJ2kT2t9NBRaH");
    private static String connectionURL = new String("jdbc:mysql://latcs7.cs.latrobe.edu.au:3306/");
    private static String driver = "com.mysql.jdbc.Driver";
    private static String dbName = new String("m7gonzalezyanez");

    /**
     * ******** My localhost DB Configuration *********
     */
//    private static DBHandler instance = null;
//    private Connection conn = null;
//    private static String username = new String("root");
//    private static String pwd = new String("root");
//    private static String connectionURL = new String("jdbc:mysql://localhost:3306/");
//    private static String driver = "com.mysql.jdbc.Driver";
//    private static String dbName = new String("spatialmarketplace");
    /*
     * Constructor
     */
    public DBHandler() {
    }

    public static DBHandler getInstance() {
        if (instance == null) {
            instance = new DBHandler();
        }
        return instance;
    }

    /**
     * Open a DB connection
     *
     * @return conn
     */
    public Connection openConnection() {
        try {

            Class.forName(driver).newInstance();
            conn = DriverManager.getConnection(connectionURL + dbName, username, pwd);
            System.out.println("Connected to the database");

        } catch (SQLException ex) {
            Logger.getLogger(DBHandler.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(DBHandler.class.getName()).log(Level.SEVERE, null, ex);
        } catch (InstantiationException ex) {
            Logger.getLogger(DBHandler.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IllegalAccessException ex) {
            Logger.getLogger(DBHandler.class.getName()).log(Level.SEVERE, null, ex);
        }
        return conn;
    }

    /**
     * Close the DB connection
     *
     */
    public void closeConnection() {
        try {
            conn.close();
            System.out.println("Disconnected from database");
        } catch (SQLException ex) {
            Logger.getLogger(DBHandler.class.getName()).log(Level.SEVERE, null, ex);
        }

    }

    /**
     * Execute the queries into the DB
     *
     * @return ResultSet
     */
    public ResultSet executeQuery(String query) {
        Statement statement = null;

        ResultSet rs = null;
        try {
            // Statements allow to issue SQL queries to the database
            statement = conn.createStatement();
            // Result set get the result of the SQL query
            rs = statement.executeQuery(query);
        } catch (SQLException ex) {
            Logger.getLogger(DBHandler.class.getName()).log(Level.SEVERE, null, ex);
        }
        return rs;
    }
}
